package onlinebanking.server.objects;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Calendar;
import java.util.GregorianCalendar;

import onlinebanking.constants.MSG;
import onlinebanking.network.Message;
import onlinebanking.server.SimpleServer;

import onlinebanking.sql.SQLConnection;
import onlinebanking.sql.SQLTable;

public class BankAccount extends QueryObject{
	/**
	 * accountno is stored in DB as int to prevent alphanumeric
	 */
	protected String accountno;
	protected int bankaccountid;
//	protected double balance; //or float dapat?? double muna para sure. di naman papansinin yan
//	protected int tac;			//wag na lang siguro muna i-load for security reason
	protected int threshold;
	
	protected boolean exists;
	
	public BankAccount(int bankaccountid){
		super();
		String sqlstatement = "SELECT bankaccountid,accountno,balance,threshold" +
				" FROM bankaccounts WHERE bankaccountid = ? ;";
		//user inherited variables
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setIntParam(1, bankaccountid);
		this.resultset = this.sqltable.executeSQL();
		_loadinfo();
		
	}
	
	public BankAccount(int bankid,int accountno) {
		super();
		String sqlstatement = "SELECT bankaccountid,accountno,balance,threshold" +
				" FROM bankaccounts WHERE accountno = ? ;";
		//user inherited variables
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setIntParam(1, accountno);			//will this work? accountno is int in DB
		this.resultset = this.sqltable.executeSQL();
		_loadinfo();
	}
	
	private void _loadinfo(){
		try {
			//if no results, return
			this.exists = resultset.first();
			if(!exists)	return;	
			
			//loads variables from db
			bankaccountid = resultset.getInt("bankaccountid");
			accountno = Integer.toString( resultset.getInt("accountno") );
			threshold = resultset.getInt("threshold");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public int getBankAccountID(){
		return bankaccountid;
	}
	
	public String getAccountno(){
		return accountno;
	}
	
	public int getThreshold(){
		return threshold;
	}
	
	public int getTAC(){
		//to be implemented
		return -1;
	}
	
	//returns the amount balance of this account
	public double getBalance() {
		double balance = 0.0;
		//update balance from db
		String sqlstatement = "SELECT balance FROM bankaccounts WHERE bankaccountid = ? ;";
		//user inherited variables
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setIntParam(1, bankaccountid);
		this.resultset = this.sqltable.executeSQL();
		try {
			if(resultset.first()){
				balance = this.resultset.getDouble("balance");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return balance;
	}
	
	public boolean setTAC(int TAC){
		String sqlstatement = "UPDATE bankaccounts SET tac = ? WHERE bankaccountid = ? ;";
		this.sqltable.setSQLStatement(sqlstatement);
		
		this.sqltable.setIntParam(1, TAC);
		this.sqltable.setIntParam(2, this.bankaccountid);
		return (this.sqltable.executeUpdate() != -1);
	}
	
	//increases the current balance, returns true on success
	public boolean depositAmmount(double ammount){
		String sqlstatement = "UPDATE bankaccounts SET balance = balance + ? WHERE bankaccountid = ?";
		
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setDoubleParam(1, ammount);
		this.sqltable.setIntParam(2, this.bankaccountid);
		
		
		
		int result = this.sqltable.executeUpdate();
		if(result != -1){
			System.out.println("depositing "+ammount +" bankaccountid updated:"+result);
		}
		
		return true;
		
	}
	
	//decreases the current balance, returns true on success
	public boolean widthrawAmmount(double ammount){
		
		//check if enough balance
		String checksqlstatement = "SELECT balance from bankaccounts WHERE bankaccountid = ?";
		this.sqltable.setSQLStatement(checksqlstatement);
		this.sqltable.setIntParam(1, this.bankaccountid);
		ResultSet rs = this.sqltable.executeSQL();
		
		double dbbalance;
		//check if enough balance
		try {
			if(rs.next()){
				dbbalance = rs.getDouble(1);
				if(dbbalance < ammount) {
					rs.close();
					return false;
				}
			}else{
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		
		String sqlstatement = "UPDATE bankaccounts SET balance = balance - ? WHERE bankaccountid = ?";
		
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setDoubleParam(1, ammount);
		this.sqltable.setIntParam(2, this.bankaccountid);
		
		int result = this.sqltable.executeUpdate();
		
		System.out.println("widthrawing "+ammount +" bankaccountid updated:"+result);
		
		return true;
	}
	
	public boolean exists(){
		return this.exists;
	}
	
	public Message getTransactionHistory(){
		Message response = new Message();
		
		String sqlstatement = "SELECT transactionid, transactiontypeid,transstatusid,expiration,initiatedby,processedon," +
				"ammounttransfer,srcaccountno,destaccountno " +
				"FROM transactions LEFT JOIN fundstransfer USING (transactionid) WHERE bankaccountid = ?;";
		
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setIntParam(1, this.bankaccountid);
		ResultSet rs = this.sqltable.executeSQL();
		
		try {
			if(rs.first()){
				int i=0;
				do{
					
					int transactionid = rs.getInt("transactionid");
					char transstatusid = rs.getString("transstatusid").toCharArray()[0];
					Timestamp expiry = rs.getTimestamp("expiration");
					int initiatedby = rs.getInt("initatedby");
					double ammounttransfer = rs.getDouble("ammounttransfer");
					
					GregorianCalendar gcal = (GregorianCalendar) Calendar.getInstance();
					if(expiry != null){
						gcal.setTime(expiry);
						int expDay = gcal.get(Calendar.DAY_OF_MONTH);
						int expMonth = gcal.get(Calendar.MONTH);
						int expYear = gcal.get(Calendar.YEAR);
					}
					
					i++;
				}while(rs.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return response;
		
	}
	
	public int[] getAccountOwners(){
		String sqlstatement = "SELECT count(personalaccountid) FROM accountowners WHERE bankaccountid = ?;";
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setIntParam(1, this.bankaccountid);
		ResultSet rs = this.sqltable.executeSQL();
		try {
		rs.first();
		int ownercount = rs.getInt(1);
		rs.close();
		
		sqlstatement = "SELECT personalaccountid FROM accountowners WHERE bankaccountid = ?;";
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setIntParam(1, this.bankaccountid);
		rs = this.sqltable.executeSQL();
		
		int owners[] = new int[ownercount];
		
		if(rs.first()){
			int i = 0;
			do{
				owners[i] = rs.getInt(1);
				i++;
			}while(rs.next());			
		}
		
		return owners;
		} catch (SQLException e) {
			return new int[0];
		}
	}
	
	/*=======================================Static methods here============================== */
	
	public static boolean verifyOwnership(int personalaccountid, int bankaccountid){
		String sqlstatement = "SELECT accountownerid FROM accountowners " +
				"WHERE bankaccountid = ? AND personalaccountid = ?; ";
		
		SQLTable sqltable = new SQLTable(SQLConnection.getInstance().getConnection());
		sqltable.setSQLStatement(sqlstatement);
		sqltable.setIntParam(1, bankaccountid);
		sqltable.setIntParam(2, personalaccountid);
		ResultSet rs = sqltable.executeSQL();
		
		try {
			return rs.next();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static int getAccountnoByBankaccountid(int bankaccountid){
		String sqlstatement = "SELECT accountno FROM bankaccounts WHERE bankaccountid = ? ;";
		
		SQLTable sqltable = new SQLTable(SQLConnection.getInstance().getConnection());
		sqltable.setSQLStatement(sqlstatement);
		sqltable.setIntParam(1, bankaccountid);
		ResultSet rs = sqltable.executeSQL();
		
		try {
			if(rs.next()){
				return rs.getInt(1);
			}else{
				return -1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		
	}
	
	public static Message getBankAccounts(int personalaccountid) {
		
		
		String sqlstatement = "SELECT bankaccountid,accountno " +
				"FROM bankaccounts JOIN accountowners USING (bankaccountid) WHERE personalaccountid = ? ;";
		
		SQLTable sqltable = new SQLTable(SQLConnection.getInstance().getConnection());
		
		sqltable.setSQLStatement(sqlstatement);
		sqltable.setIntParam(1, personalaccountid);
		ResultSet resultset = sqltable.executeSQL();
		
		
		Message response = new Message();
		response.setMsgType(MSG.MY_BANK_ACCOUNTS);
		response.setMsgSubType(MSG.LIST_OF_BANKACCOUNTS);
		
		//no of results
		int bankaccount_count = 0;
		try {
			while(resultset.next()){
				bankaccount_count++;
				int intaccountno = resultset.getInt("accountno");
				
				//add the accountno to the message with #bankaccount_count as key
				response.addAttribute("#"+bankaccount_count,Integer.toString(intaccountno));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		response.addAttribute("count", bankaccount_count);
		
		return response;
	}

	public static BankAccount createNewBankAccount(int threshold, double initAmmount){
		/*
		 * Account no and TAC are system generated
		 */
		if(threshold < 1)	return null;
		String maxAccountnoSQL = "SELECT MAX(accountno) FROM bankaccounts;";
		
		SQLTable sqltable = new SQLTable(SQLConnection.getInstance().getConnection());
		
		sqltable.setSQLStatement(maxAccountnoSQL);
		
		ResultSet rs = sqltable.executeSQL();
		int newAccountNo = -1;
		try {
			if(rs.next()){
				newAccountNo = rs.getInt(1);				
			}else{
				newAccountNo = 1; // default starting account no
			}
		} catch (SQLException e) {
			return null;
		}
		
		// INSERT NEW BANK ACCOUNT
		String insertSQL = "INSERT INTO bankaccounts(accountno,threshold,balance) VALUES (?,?,?);";
		sqltable.setSQLStatement(insertSQL);
		sqltable.setIntParam(1, newAccountNo);
		sqltable.setIntParam(2, threshold);
		sqltable.setDoubleParam(3, initAmmount);
		
		int newbankaccountid = sqltable.executeUpdate();
		if (newbankaccountid == -1){
			return null;
		}else{
			BankAccount newBA = new BankAccount(newbankaccountid);
			return newBA;
		}
		
	}
	
	
	//==================================FOR TESTING PURPOSES  ==================================================
	public static void main(String[] args) throws SQLException {
		System.out.println("main-BankAccount");
//		SQLConnection sqlconn =  new SQLConnection();
//		SQLTable sqltable = new SQLTable(sqlconn.getConnection());
		System.out.println("READY");
		
		Message msg = BankAccount.getBankAccounts(2);
		int count = msg.getInt("count");
		for(int i = 1;i<=count;i++){
			System.out.println("account:"+msg.getString("#"+i));
		}
		
		
		
	}
}
